package data.dao.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import data.dao.DBManager;
import data.dao.IEventDAO;
import data.model.Event;

public class DBEventDAO implements IEventDAO{
    private static final Logger LOGGER = Logger.getLogger(DBEventDAO.class.getName());
    
    public static final String TABLE_NAME         = "EVENTS";
    
    private static final String ID_FIELD_NAME     = "ID";
    private static final String MP_FIELD_NAME     = "MP";
    private static final String WEIGHT_FIELD_NAME = "WEIGHT";
    private static final String NOM_FIELD_NAME    = "NOM";
    private static final String DEBUT_FIELD_NAME  = "DEBUT";//Date
    private static final String FIN_FIELD_NAME    = "FIN";//Date   
    private static final String CLIENT_FIELD_NAME = "CLIENT";

    
    private static final String ID_FIELD_TYPE     = "INTEGER";
    private static final String MP_FIELD_TYPE     = "LONG";
    private static final String WEIGHT_FIELD_TYPE = "LONG";    
    private static final String NOM_FIELD_TYPE    = "TEXT";    
    
    private static final String DEBUT_FIELD_TYPE  = "INTEGER";
    private static final String FIN_FIELD_TYPE    = "INTEGER";
    private static final String CLIENT_FIELD_TYPE = "TEXT"; 
  
    
   
    
    public static final String CREATE_TABLE_STATEMENT = ID_FIELD_NAME + " " + ID_FIELD_TYPE 
	+ ", " + WEIGHT_FIELD_NAME        + " " + WEIGHT_FIELD_TYPE 
	+ ", " + MP_FIELD_NAME            + " " + MP_FIELD_TYPE 
	+ ", " + DEBUT_FIELD_NAME         + " " + DEBUT_FIELD_TYPE 
	+ ", " + FIN_FIELD_NAME           + " " + FIN_FIELD_TYPE 
	+ ", " + NOM_FIELD_NAME           + " " + NOM_FIELD_TYPE 
	+ ", " + CLIENT_FIELD_NAME        + " " + CLIENT_FIELD_TYPE ;

    
    private PreparedStatement GET_ALL_EVENTS_PSTATEMENT    = null;
    private PreparedStatement GET_USER_EVENTS_PSTATEMENT   = null;
    private PreparedStatement UPDATE_EVENT_PSTATEMENT      = null;
    private PreparedStatement INSERT_EVENT_PSTATEMENT      = null;
    private PreparedStatement DELETE_ALL_EVENTS_PSTATEMENT = null;
    
    private int lastId = Event.NO_ID;
    
    public DBEventDAO() {
    }
    
    public List<Event> getEvents() {
	return getEventsWithIDFilter(null);
    }
    
    public Event getEvent(int id) {
	Collection<Event> events = getEventsWithIDFilter(id);
	Event event;
	if (events.size() == 1) {
	    event = events.iterator().next();
	} else {
	    event = null;
	}
	return event;
    }
    
    private List<Event> getEventsWithIDFilter(Integer id) {
	if (GET_ALL_EVENTS_PSTATEMENT == null) {
	    GET_ALL_EVENTS_PSTATEMENT = DBManager.INSTANCE.prepareStatement("SELECT * from " 
									    + TABLE_NAME 
									    + " order by "
									    + ID_FIELD_NAME);
	}
	if (GET_USER_EVENTS_PSTATEMENT == null) {
	    GET_USER_EVENTS_PSTATEMENT = DBManager.INSTANCE.prepareStatement("SELECT * from " 
									     + TABLE_NAME 
									     + " where "
									     + ID_FIELD_NAME 
									     + "=?" 
									     + " order by " 
									     + ID_FIELD_NAME);
	}
	
	ResultSet resultSet;
	try {
	    PreparedStatement pStatement;
	    if (id != null) {
		pStatement = GET_USER_EVENTS_PSTATEMENT;
		pStatement.setInt(1, id);
		
	    } else {
		pStatement = GET_ALL_EVENTS_PSTATEMENT;
	    }
	    
	    resultSet = pStatement.executeQuery();
	} catch (SQLException e) {
	    LOGGER.log(Level.SEVERE, "Error while getting events", e);
	    resultSet = null;
	}
	
	List<Event> result;
	if (resultSet == null) {
	    result = new ArrayList<Event>();
	} else {
	    result = new ArrayList<Event>();
	    try {
		while (resultSet.next()) {
		    Event event = new Event();
		    event.setId(resultSet.getInt(ID_FIELD_NAME));
		    // event.setWeight(resultSet.getLong(WEIGHT_FIELD_NAME));
		    //event.setMp(resultSet.getLong(MP_FIELD_NAME));
		    //event.setNom(resultSet.getString(NOM_FIELD_NAME));
		   
		    //event.setDebut(new Date(resultSet.getLong(DEBUT_FIELD_NAME)));//Date
		    //event.setFin(new Date(resultSet.getLong(FIN_FIELD_NAME)));//Date
		    //event.setClient(resultSet.getString(CLIENT_FIELD_NAME));
		   		    
		    result.add(event);
		}
	    } catch (SQLException e) {
		e.printStackTrace();
		result = null;
	    }
	}
	
	return result;
    }
    
    private int getNextEventId() {
	if (lastId == Event.NO_ID) {
	    Collection<Event> events = getEvents();
	    for (Iterator<Event> eventIte = events.iterator(); eventIte.hasNext();) {
		Event event = eventIte.next();
		if (event.getId() > lastId) {
		    lastId = event.getId();
		}
	    }
	}
	return ++lastId;
    }
    

    public boolean modify(Event event){
	 // modification
	boolean result = true;
	try {
	    if (UPDATE_EVENT_PSTATEMENT == null) {
		UPDATE_EVENT_PSTATEMENT = DBManager.INSTANCE.prepareStatement("UPDATE " 
									      + TABLE_NAME 
									      + " SET "
									      + WEIGHT_FIELD_NAME 
									      + "=?, " 
									      + MP_FIELD_NAME 
									      + "=?, " 
									      + DEBUT_FIELD_NAME 
									      + "=?, "
									      + FIN_FIELD_NAME 
									      + "=?, " 
									      + CLIENT_FIELD_NAME 
									      + "=?, " 	      
									      + " where " 
									      + ID_FIELD_NAME 
									      + "=?");
	    } else {
		UPDATE_EVENT_PSTATEMENT.clearParameters();
	    }
	    
	    UPDATE_EVENT_PSTATEMENT.setInt(1,    event.getId());
	    UPDATE_EVENT_PSTATEMENT.setLong(2,   event.getWeight());
	    UPDATE_EVENT_PSTATEMENT.setLong(3,   event.getMp());
	    UPDATE_EVENT_PSTATEMENT.setString(4, event.getNom());
	    UPDATE_EVENT_PSTATEMENT.setLong(5,   event.getDebut().getTime());//Date		    
	    UPDATE_EVENT_PSTATEMENT.setLong(6,   event.getFin().getTime());//Date	
	    UPDATE_EVENT_PSTATEMENT.setString(7, event.getClient());	
	   	    
	    
	    result = UPDATE_EVENT_PSTATEMENT.executeUpdate() >= 1;
	    
	} catch (SQLException e) {
	    LOGGER.log(Level.SEVERE, "Error while updating event", e);
	    result = false;
	}

	return result;
	
    }
    





    public boolean add(Event event) {
	boolean result = true;
	
	if (event.getId() > Event.NO_ID) {
	    event.setId(getNextEventId());
	    
	    try {
		if (INSERT_EVENT_PSTATEMENT == null) {
		    INSERT_EVENT_PSTATEMENT = DBManager.INSTANCE.prepareStatement("INSERT INTO " 
										  + TABLE_NAME 
										  + " ("
										  + ID_FIELD_NAME 
										  + ", " 
										  + WEIGHT_FIELD_NAME 
										  + ", " 
										  + MP_FIELD_NAME 
										  + ", "

										  + DEBUT_FIELD_NAME 
										  + ", " 
										  + FIN_FIELD_NAME 
										  + ", " 
										  + CLIENT_FIELD_NAME 
										  + ") values (?, ?, ?, ?, ?, ?)");
		} else {
		    INSERT_EVENT_PSTATEMENT.clearParameters();
		}
		
		INSERT_EVENT_PSTATEMENT.setInt(1,    event.getId());
		INSERT_EVENT_PSTATEMENT.setLong(2,   event.getWeight());
		INSERT_EVENT_PSTATEMENT.setLong(3,   event.getMp());
		INSERT_EVENT_PSTATEMENT.setString(4, event.getNom());
		INSERT_EVENT_PSTATEMENT.setLong(5,   event.getDebut().getTime());//Date	
		INSERT_EVENT_PSTATEMENT.setLong(6,   event.getFin().getTime());//Date	
		//INSERT_EVENT_PSTATEMENT.setString(7, event.getClient());	
			
	    		
		result = INSERT_EVENT_PSTATEMENT.executeUpdate() >= 1;
	    } catch (SQLException e) {
		LOGGER.log(Level.SEVERE, "Error while inserting event", e);
		result = false;
	    }
	}
	
	return result;
    }
    
    public boolean deleteAllEvents() {
	if (DELETE_ALL_EVENTS_PSTATEMENT == null) {
	    DELETE_ALL_EVENTS_PSTATEMENT = DBManager.INSTANCE.prepareStatement("DELETE FROM " + TABLE_NAME);
	}
	boolean result;
	try {
	    result = DELETE_ALL_EVENTS_PSTATEMENT.executeUpdate() >= 1;
	} catch (SQLException e) {
	    LOGGER.log(Level.SEVERE, "Error while deleting all events", e);
	    result = false;
	}
	if (result) {
	    lastId = Event.NO_ID;
	}
	return result;
    }
}
